from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Date, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref
import os
 
if (os.path.exists('Inscritos_2010-2011-bdd.db')):
    os.unlink('Inscritos_2010-2011-bdd.db')

# - Cria e liga-se a BDD, sem print -> echo
engine = create_engine('sqlite:///Inscritos_2010-2011-bdd.db', echo = False)
Base = declarative_base()

# - Classe mapeada que cria a bdd e faz bind com o python
class CLS_Table(Base):
    # Table Name
    __tablename__ = "elementsbdd"
    # BDD Fields
    id = Column(Integer, primary_key = True)
    estabelecimento = Column(String)
    unidade = Column(String)
    nivel = Column(String)
    curso = Column(String)
    area = Column(String)
    year_9596h = Column(String)
    year_9596m = Column(String)
    year_9596hm = Column(String)
    year_9697h = Column(String)
    year_9697m = Column(String)
    year_9697hm = Column(String)
    year_9798h = Column(String)
    year_9798m = Column(String)
    year_9798hm = Column(String)
    year_9899h = Column(String)
    year_9899m = Column(String)
    year_9899hm = Column(String)
    year_9900h = Column(String)
    year_9900m = Column(String)
    year_9900hm = Column(String)
    year_0001h = Column(String)
    year_0001m = Column(String)
    year_0001hm = Column(String)
    year_0102h = Column(String)
    year_0102m = Column(String)
    year_0102hm = Column(String)
    year_0203h = Column(String)
    year_0203m = Column(String)
    year_0203hm = Column(String)
    year_0304h = Column(String)
    year_0304m = Column(String)
    year_0304hm = Column(String)
    year_0405h = Column(String)
    year_0405m = Column(String)
    year_0405hm = Column(String)
    year_0506h = Column(String)
    year_0506m = Column(String)
    year_0506hm = Column(String)
    year_0607h = Column(String)
    year_0607m = Column(String)
    year_0607hm = Column(String)
    year_0708h = Column(String)
    year_0708m = Column(String)
    year_0708hm = Column(String)
    year_0809h = Column(String)
    year_0809m = Column(String)
    year_0809hm = Column(String)
    year_0910h = Column(String)
    year_0910m = Column(String)
    year_0910hm = Column(String)
    year_1011h = Column(String)
    year_1011m = Column(String)
    year_1011hm = Column(String)
 
    # Init Values Class to Insert BDD
    def __init__(self, estabelecimento, unidade, nivel, curso, area, year_9596h, year_9596m, year_9596hm, year_9697h, year_9697m, year_9697hm
                , year_9798h, year_9798m, year_9798hm, year_9899h, year_9899m, year_9899hm, year_9900h, year_9900m, year_9900hm, year_0001h
                , year_0001m, year_0001hm, year_0102h, year_0102m, year_0102hm, year_0203h, year_0203m, year_0203hm, year_0304h, year_0304m
                , year_0304hm, year_0405h, year_0405m, year_0405hm, year_0506h, year_0506m, year_0506hm, year_0607h, year_0607m, year_0607hm
                , year_0708h, year_0708m, year_0708hm, year_0809h, year_0809m, year_0809hm, year_0910h, year_0910m, year_0910hm, year_1011h
                , year_1011m, year_1011hm):

        self.estabelecimento = estabelecimento
        self.unidade = unidade
        self.nivel = nivel
        self.curso = curso
        self.area = area
        self.year_9596h = year_9596h
        self.year_9596m = year_9596m
        self.year_9596hm = year_9596hm
        self.year_9697h = year_9697h
        self.year_9697m = year_9697m
        self.year_9697hm = year_9697hm
        self.year_9798h = year_9798h
        self.year_9798m = year_9798m
        self.year_9798hm = year_9798hm
        self.year_9899h = year_9899h
        self.year_9899m = year_9899m
        self.year_9899hm = year_9899hm
        self.year_9900h = year_9900h
        self.year_9900m = year_9900m
        self.year_9900hm = year_9900hm
        self.year_0001h = year_0001h
        self.year_0001m = year_0001m
        self.year_0001hm = year_0001hm
        self.year_0102h = year_0102h
        self.year_0102m = year_0102m
        self.year_0102hm = year_0102hm
        self.year_0203h = year_0203h
        self.year_0203m = year_0203m
        self.year_0203hm = year_0203hm
        self.year_0304h = year_0304h
        self.year_0304m = year_0304m
        self.year_0304hm = year_0304hm
        self.year_0405h = year_0405h
        self.year_0405m = year_0405m
        self.year_0405hm = year_0405hm
        self.year_0506h = year_0506h
        self.year_0506m = year_0506m
        self.year_0506hm = year_0506hm
        self.year_0607h = year_0607h
        self.year_0607m = year_0607m
        self.year_0607hm = year_0607hm
        self.year_0708h = year_0708h
        self.year_0708m = year_0708m
        self.year_0708hm = year_0708hm
        self.year_0809h = year_0809h
        self.year_0809m = year_0809m
        self.year_0809hm = year_0809hm
        self.year_0910h = year_0910h
        self.year_0910m = year_0910m
        self.year_0910hm = year_0910hm
        self.year_1011h = year_1011h
        self.year_1011m = year_1011m
        self.year_1011hm = year_1011hm
 
# - Criar tabelas
Base.metadata.create_all(engine)